package DatabaseManager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import Helper.Misc;

public class DBManager {

	private static final int numberOfInsertsPerBatch = 2000;
	private int insertCtr = 0;
	private ArrayList<String> statmentList = new ArrayList<String>();

	/**
	 * 
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 *             Opens a connection with the database
	 */
	public DBManager() throws SQLException, ClassNotFoundException {
		OpenDBConnection();
	}

	/**
	 * Overriding finalize, so that the connection is always closed
	 */
	@Override
	public void finalize() throws SQLException {
		CloseDBConnection();
	}

	/**
	 * Creates a new table (doesn't load any data yet)
	 * 
	 * @param table_name
	 *            - name of the table
	 * @param columns
	 *            - names of all attributes
	 * @param primaryKey
	 *            - name of primary key
	 * @param foreignKey
	 *            - the i'th place is not null if the i'th attribute is a
	 *            foreign key. In that case, it contains what the foreign key
	 *            refers to
	 * @param dataTypes
	 *            - names of the datatype of each attribute
	 * @param isDataTypeNull
	 *            - true <=> an attribute can be null
	 * @param isMyISAM
	 *            - true <=> the table is MyISAM
	 * @throws SQLException
	 */
	// creating new table (table name and columns), assumes there is at least
	// one column
	public void CreateTable(String table_name, String[] columns,
			String primaryKey, String[] foreignKey, String[] dataTypes,
			boolean[] isDataTypeNull, boolean isMyISAM) throws SQLException {
		// setting command - every table is assigned with a primary key
		String create_string = "create table " + table_name + " (";

		// add each column name to command
		for (int i = 0; i < columns.length; i++) {
			create_string += columns[i] + " " + dataTypes[i] + " "
					+ (isDataTypeNull[i] ? "" : "NOT ") + "NULL"
					+ (columns[i].equals(primaryKey) ? " AUTO_INCREMENT" : "");

			// if this is the last column close the (), otherwise add ','
			if (i < columns.length - 1)
				create_string += ", ";
		}

		// adding primary key
		if (primaryKey != null)
			create_string += ", PRIMARY KEY (" + primaryKey + ")";

		// adding foreign keys
		for (int i = 0; i < foreignKey.length; i++)
			if (foreignKey[i] != null)
				create_string += ", FOREIGN KEY (" + columns[i]
						+ ") REFERENCES " + foreignKey[i]
						+ "ON DELETE CASCADE ON UPDATE NO ACTION";
		create_string += ")";
		if (isMyISAM)
			create_string += " ENGINE = MyISAM;";

		ExecuteStatment(create_string);
	}

	/**
	 * Drops a table from the database.
	 * 
	 * @param table_name
	 *            - table name to be dropped
	 * @throws SQLException
	 */
	public void DeleteTable(String table_name) throws SQLException {
		// setting command
		String delete_string = "drop table " + table_name;
		ExecuteStatment(delete_string);
	}

	/**
	 * Inserts values into table and returns some inserted value (always used to
	 * retrieve the id)
	 * 
	 * @param table_name
	 *            - name of table to insert to
	 * @param columns
	 *            - names of attributes we insert to
	 * @param values
	 *            - values we insert to columns
	 * @param retValueName
	 *            - name of value to be returned, or null if irrelevant
	 * @return value insert in retValueName (again, always used with id) or 0 if
	 *         retValueName is null
	 * @throws SQLException
	 */
	public int InsertTableAndReturnID(String table_name, String[] columns,
			String[] values, String retValueName) throws SQLException {
		
		String insert_string = getInsertStatement(table_name, columns, values, false);
		// decides whether to return value or just 0
		if (retValueName != null)
			return ExecuteStatmentAndGetID(insert_string, retValueName);
		else
			ExecuteStatment(insert_string);
		return 0;
	}

	/**
	 * Insert values into a table
	 * 
	 * @param table_name
	 *            - name of the table
	 * @param columns
	 *            - attributes to which we insert values
	 * @param values
	 *            - values to be inserted
	 * @throws SQLException
	 */
	public void InsertTable(String table_name, String[] columns, String[] values)
			throws SQLException {
		InsertTableAndReturnID(table_name, columns, values, null);
	}

	/**
	 * Inserts values into a table using a query (not specifying the actual
	 * values)
	 * 
	 * @param table_name
	 *            - name of the table
	 * @param query
	 *            - query whose results we insert into the table
	 * @param attributes
	 *            - attributes where we put the values returned from the query
	 * @throws SQLException
	 */
	public void InsertTableUsingQuery(String table_name, String query,
			String[] attributes) throws SQLException {
		String stmt = "INSERT INTO " + table_name + "(";
		for (int i = 0; i < attributes.length; i++) {
			stmt += attributes[i];
			if (i != attributes.length - 1)
				stmt += ", ";
		}
		stmt += ") " + query;
		ExecuteStatment(stmt);

	}

	/**
	 * inserts to a table data (values) into columns
	 * @param table_name - name of table
	 * @param columns - names of columns
	 * @param values - data to columns
	 * @throws SQLException
	 */
	public void batchInsert(String table_name, String[] columns, String[] values)
			throws SQLException {
		String insert_string = getInsertStatement(table_name, columns, values, true);
		batchExecuteStatment(insert_string);
	}

	/**
	 * Finishes batching and executes final batch while dumping data from file.
	 * @throws SQLException
	 */
	protected void finalizeBatchInsert() throws SQLException {
		if (0 < insertCtr) {
			java.sql.Statement stmt;
			stmt = conn.createStatement();

			for (String s : statmentList)
				stmt.addBatch(s);

			stmt.executeBatch();
			statmentList.clear();
			insertCtr = 0;
			stmt.close();
		}
	}

	/**
	 * Executes a statement during dumping from file (actually batches it)
	 * @throws SQLException
	 */
	private void excecuteBatch() throws SQLException {
		java.sql.Statement stmt;
		stmt = conn.createStatement();
		for (String s : statmentList)
			stmt.addBatch(s);

		stmt.executeBatch();
		stmt.close();
	}

	/**
	 * Controls statement executing while dumping data from file.
	 * @param statment
	 * @throws SQLException
	 */
	private void batchExecuteStatment(String statment) throws SQLException {
		statmentList.add(statment + ";");
		insertCtr++;
		if (insertCtr == numberOfInsertsPerBatch) {
			excecuteBatch();
			insertCtr = 0;
			statmentList.clear();
		}
	}

	/**
	 * Updates the table with the specified values
	 * 
	 * @param table_name
	 *            - name of the table to be updated
	 * @param column
	 *            - names of column to be updated
	 * @param value
	 *            - values for column
	 * @param where
	 *            - where to update
	 * @throws SQLException
	 */
	public void UpdateTable(String table_name, String[] column,
			String[] values, String where) throws SQLException {

		String update_string = "update " + table_name + " set ";
		for (int i = 0; i < column.length; i++) {
			update_string += column[i] + "=" + values[i];
			if (i != column.length - 1)
				update_string += ", ";
		}
		update_string += " where " + where;
		ExecuteStatment(update_string);
	}

	/**
	 * Clears a table (empties it)
	 * 
	 * @param table_name
	 *            - name of the table to be cleared
	 * @throws SQLException
	 */
	public void ClearTable(String table_name) throws SQLException {
		String del_string = "DELETE FROM " + table_name;

		ExecuteStatment(del_string);
	}

	/**
	 * Deletes row from a table
	 * 
	 * @param table_name
	 *            - name of the table
	 * @param where
	 *            - condition regarding which rows will be deleted
	 * @throws SQLException
	 */
	public void DeleteFromTable(String table_name, String where)
			throws SQLException {
		String del_string = "DELETE FROM " + table_name + " WHERE " + where;

		ExecuteStatment(del_string);
	}

	/**
	 * Executes a query.
	 * 
	 * @param query
	 *            - the query to be executed
	 * @return the result set of the query
	 * @throws SQLException
	 */
	public ResultSet QueryTable(String query) throws SQLException {
		ResultSet rs;

		lastStmt = conn.createStatement();
		rs = lastStmt.executeQuery(query);
		return rs;
	}

	/**
	 * Adds a foreign key constraint to a table.
	 * @param tableName - name of the table
	 * @param constraintName - name of the constraint
	 * @param column - column that is foreign key 
	 * @param references - format: table(column) that the foreign key references
	 * @throws SQLException
	 */
	public void addForeignKeyConstraint(String tableName,
			String constraintName, String column, String references)
			throws SQLException {
		String statement = "ALTER TABLE " + tableName + " ADD CONSTRAINT ";
		statement += constraintName + " FOREIGN KEY (" + column + ")";
		statement += " REFERENCES " + references;
		statement += " ON DELETE CASCADE ON UPDATE NO ACTION";
		ExecuteStatment(statement);
	}

	/**
	 * Adds a unique constraint to a table.
	 * @param tableName - name of the table
	 * @param column - column to add unique constraint to
	 * @throws SQLException
	 */
	public void addUniqueConstraint(String tableName, String column)
			throws SQLException {
		String statement = "ALTER TABLE " + tableName + " ADD UNIQUE ("
				+ column + ")";
		ExecuteStatment(statement);
	}

	/**
	 * Creates an index in a table
	 * @param table_name - name of the table
	 * @param column - index is defined on this
	 * @param type - type of index (hash, btree)
	 * @param indexName - name of the index
	 * @throws SQLException
	 */
	public void CreateIndex(String table_name, String column, String type,
			String indexName) throws SQLException {
		String statement = "ALTER TABLE " + table_name;
		statement += " ADD INDEX " + indexName + " USING " + type;
		statement += "(" + column + " ASC)";
		ExecuteStatment(statement);
	}

	/**
	 * Changes a table's engine to InnoDB
	 * @param table_name - name of the table
	 * @throws SQLException
	 */
	public void changeTableToInnoDB(String table_name) throws SQLException {
		String statement = "ALTER TABLE " + table_name + " ENGINE = InnoDB";
		ExecuteStatment(statement);
	}

	/**
	 * Sets the value for auto committing.
	 * 
	 * @param value
	 *            - the value to set to
	 * @throws SQLException
	 */
	public void setAutoCommit(boolean value) throws SQLException {
		conn.setAutoCommit(value);
	}

	/**
	 * Rolls back the current transaction.
	 * 
	 * @throws SQLException
	 */
	public void rollback() throws SQLException {
		conn.rollback();
	}

	/**
	 * Commits the current transaction.
	 * 
	 * @throws SQLException
	 */
	public void commit() throws SQLException {
		conn.commit();
	}
	
	/**
	 * Gets the inserts statement given the parameters.
	 * @param table_name - name of the table.
	 * @param columns - columns we add values to
	 * @param values - values to add
	 * @param doCorrect - true <=> values should be corrected (regarding gereshim)
	 * @return
	 */
	private String getInsertStatement(String table_name, String[] columns,
			String[] values, boolean doCorrect) {
		String stat_columns = "(";
		String stat_values = "(";
		// build insert string
		for (int i = 0; i < values.length; i++) {
			// building (..columns...) VALUES (..values)
			stat_columns += columns[i];

			// don't insert "" insert NULL instead
			if (values[i].length() == 0) {
				stat_values += "NULL";
			} else {
				// filtering out problematic characters & building value string
				if (doCorrect)
					values[i] = Misc.correctString(values[i]);
				stat_values += "'" + values[i] + "'";
			}

			// if this is the last value close the (), otherwise add ','
			if (i < values.length - 1) {
				stat_columns += ",";
				stat_values += ",";
			}
		}
		String insert_string = "insert into " + table_name + stat_columns
				+ ") values" + stat_values + ")";
		return insert_string;
	}

	/**
	 * Executes an SQL statement.
	 * 
	 * @param statement
	 *            - the statement to be executed.
	 * @throws SQLException
	 */
	private void ExecuteStatment(String statement) throws SQLException {
		java.sql.Statement stmt;

		stmt = conn.createStatement();
		// mechanism to ensure stmt's closing
		try {
			stmt.executeUpdate(statement);
		} catch (SQLException e) {
			stmt.close();
			throw e;
		}
		stmt.close();
	}

	/**
	 * Executes a statement and returns the inserted value for retValueName
	 * (always called with id)
	 * 
	 * @param statement
	 *            - statement to execute.
	 * @param retValueName
	 *            - name of attribute for which we want the inserted value.
	 * @return the inserted value for retValueName
	 * @throws SQLException
	 */
	private int ExecuteStatmentAndGetID(String statement, String retValueName)
			throws SQLException {
		java.sql.Statement stmt = null;
		ResultSet rs = null;
		int ret = -1;

		try {
			stmt = conn.createStatement();
			stmt.executeUpdate(statement, new String[] { retValueName });
			rs = stmt.getGeneratedKeys();
			rs.next();
			// this method is always called for retrieving an int
			ret = rs.getInt(1);
			rs.close();
			rs = null;
			stmt.close();
			stmt = null;
			// mechanism to ensure everything is closed
		} catch (SQLException e) {
			try {
				if (stmt != null)
					stmt.close();
				if (rs != null) {
					rs.close();
					rs = null;
				}
			} catch (SQLException e1) {
				if (rs != null)
					rs.close();
			}
		}
		return ret;
	}

	/**
	 * Returns a prepared statement, for the statement stmt
	 * 
	 * @param stmt
	 * @return the prepared statement
	 * @throws SQLException
	 */
	public java.sql.PreparedStatement getPreparedStatement(String stmt)
			throws SQLException {
		return conn.prepareStatement(stmt);
	}

	/**
	 * 
	 * @return Last statement (only for queries) executed
	 */
	public java.sql.Statement getLastStatement() {
		return this.lastStmt;
	}

	/**
	 * Sets last statement to the given statement
	 * @param stmt - statement to set last statement to
	 */
	public void setLastStatement(java.sql.Statement stmt) {
		this.lastStmt = stmt;
	}

	/**
	 * Closes the last statement (query statement)
	 * @throws SQLException
	 */
	public void closeLastStatement() throws SQLException {
		if (lastStmt != null) {
			java.sql.Statement temp = lastStmt;
			lastStmt = null;
			temp.close();
		}
	}

	/**
	 * Opens a connection with the database (called by the constructor)
	 * 
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	private void OpenDBConnection() throws SQLException, ClassNotFoundException {

		// if connection already opened
		if (conn != null)
			return;

		Class.forName("com.mysql.jdbc.Driver");

		// open DB connection
		conn = DriverManager.getConnection(DB_CONNECTION_STRING, DB_USER_NAME,
				DB_PASSWORD);
	}

	/**
	 * Closes the connection with the database
	 * 
	 * @throws SQLException
	 */
	private void CloseDBConnection() throws SQLException {
		if (conn != null)
			conn.close();
	}

	// conn is the connection, lastStmt saves last query statement
	private Connection conn = null;
	private java.sql.Statement lastStmt = null;

	public static String DB_CONNECTION_STRING;
	public static String DB_USER_NAME;
	public static String DB_PASSWORD;


}